package com.situ.wms.util;

import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DBUtil {
	private static DataSource datasource;
	static {
		Properties ps = new Properties();
		try {
			ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.pts2"));
			datasource = DruidDataSourceFactory.createDataSource(ps);
		} catch (Exception e) {
			System.out.println("数据库连接出错！");
			e.printStackTrace();
		}

	}

	private static Connection getConnection() throws SQLException {
		return datasource.getConnection();
	}
	public static int executeDML(String sql,Object...params)
	{
		Connection conn =null;
		PreparedStatement ps=null;
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			if(params!=null)
			{
				for(int i=0;i<params.length;i++) {
					ps.setObject(i+1, params[i]);
				}
			}
			return ps.executeUpdate();
		}catch(Exception e) {
			System.out.println("executeDML函数出错了！快来看一下");
			e.printStackTrace();
		}finally {
			close(ps,conn);
		}
		return 0;
		
	}

	

	private static void close(PreparedStatement ps, Connection conn) {
		close(null, ps, conn);
	}

	private static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			System.out.println("资源回收出错！");
		}
	}
	public static Long executeCount(String sql,Object...params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if(params != null) {
				for(int i = 0;i < params.length; i++) {
					ps.setObject(i+1, params[i]);
				}
			}
			rs = ps.executeQuery();
			
			rs.next(); //指向结果集第一行
			return rs.getLong(1);
			
		}catch(Exception e) {
			return 0L;
		}finally {
			close(rs,ps,conn);
		}
	}
	public static <T> List<T> executeDQL(String sql,Class<T> c,Object...params) {
		List<T> list=new ArrayList<>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs= null;
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			if(params!=null) {
				for(int i=0;i<params.length;i++)
				{
					ps.setObject(i+1,params[i]);//第一个参数替换第几个问号
				}
			}
			rs=ps.executeQuery();
			ResultSetMetaData rsmd=rs.getMetaData();//结果集获取的原数据，描述的结构
			while(rs.next()) {
				T obj=c.newInstance();
				for(int i=1 ;i<=rsmd.getColumnCount();i++)
				{
				Object value=rs.getObject(i);//第i列的值
				String label=rsmd.getColumnLabel(i);//第i列的表头名字
				String label2=new String();
					label2=	DBUtil.getDBname(label);
				try {
				Field field=c.getDeclaredField(label);//找到跟表头名重名的成员变量
				field.setAccessible(true);
				field.set(obj, value);
				}catch(Exception e) {
					System.out.println(label+"这一列没找到");
				}
				}
				list.add(obj);
			}
		}catch(Exception e){
			System.out.println("executeDQL函数出错了！");
			e.printStackTrace();
			
		}finally {
			DBUtil.close(rs,ps,conn);
		}
		return list;
	}
	private static String getDBname(String s1) {
		char [] arr=s1.toCharArray();
		StringBuffer s2=new StringBuffer();
		for(int i=0;i<arr.length;i++) {
			if(arr[i]=='_') {
				s2.append(String.valueOf(arr[i+1]).toUpperCase());
				i++;
			}else {
				s2.append(arr[i]);
			}
		}
		return s2.toString();
	}
	public static BigDecimal executeSum(String sql) {
		// TODO Auto-generated method stub
		Connection conn =null;
		PreparedStatement ps=null;
		ResultSet rs = null;
		ResultSetMetaData rsmd=null;
		
			try {
				conn=DBUtil.getConnection();
				ps=conn.prepareStatement(sql);
				BigDecimal x=null;
				rs= ps.executeQuery();
				rsmd=rs.getMetaData();
				while(rs.next()) {
				 x=(BigDecimal) rs.getObject(1);
				 
				}
				System.out.println(x);
				return x;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		return null;
	}
	public static String md5(String str) {
		try {
			MessageDigest md =MessageDigest.getInstance("MD5");
			md.update(str.getBytes("UTF-8"));
			return new BigInteger(1,md.digest()).toString(16);
		}catch(NoSuchAlgorithmException|UnsupportedEncodingException ignored) {
			
		}
		return null;
	}
}
